package commons.base.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.AbstractListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class Database {

	private QueryRunner qr;

	public Database(DataSource ds) {
		super();
		this.qr = new QueryRunner(ds);
	}
	
	public <T> T queryField(String sql, Object...params)throws SQLException{
    	return qr.query(sql, new ScalarHandler<T>(), params);
	}
	
	public <T> List<T> queryColumn(String sql, Object...params)throws SQLException{
		return qr.query(sql, new AbstractListHandler<T>(){
			@SuppressWarnings("unchecked")
			@Override
			protected T handleRow(ResultSet rs) throws SQLException {
				return (T)rs.getObject(1);
			}
			
		}, params);
	}
	
	public Map<String, Object> queryMultiField(String sql, Object...params)throws SQLException{
		return qr.query(sql, new MapHandler(), params);
	}
	
	public List<Map<String, Object>> queryMultiColumn(String sql, Object...params)throws SQLException{
		return qr.query(sql, new MapListHandler(), params);
	}
	
	public <T> T queryModel(String sql, Class<T> clazz, Object...params)throws SQLException{
		return qr.query(sql, new BeanHandler<T>(clazz), params);
	}
	
	public <T> List<T> queryModelList(String sql, Class<T> clazz, Object...params)throws SQLException{
		return qr.query(sql, new BeanListHandler<T>(clazz), params);
	}
	
	public int update(String sql, Object...params) throws SQLException{
		return qr.update(sql, params);
	}
	
	public long insertReturnId(String sql, Object...params)throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			conn = qr.getDataSource().getConnection();
			ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);	
			
			if((params != null) && (params.length > 0)){
				for(int i=0; i<params.length; i++){
					ps.setObject(i+1, params[i]);
				}
			}
			
			ps.executeUpdate();
			rs = ps.getGeneratedKeys();
			return rs.next() ? rs.getLong(1) : -1;
		}finally{
			DbUtils.closeQuietly(rs);
			DbUtils.closeQuietly(ps);
			DbUtils.closeQuietly(conn);
		}
	}

	public QueryRunner getQr() {
		return qr;
	}
	
	public TransactionOp beginTransaction() throws SQLException{
		return new TransactionOp(qr);
	}
	
}
